package com.situ.hospital.dao.Impl;

import com.situ.hospital.dao.IMedicineDAO;
import com.situ.hospital.pojo.Medicine;
import com.situ.hospital.pojo.query.MedicineQuery;
import com.situ.hospital.pojo.vo.MedicineIdVO;
import com.situ.hospital.pojo.vo.MedicineVO;
import com.situ.hospital.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class MedicineDAOImpl implements IMedicineDAO {

    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<MedicineVO> selectByPage(MedicineQuery medicineQuery) {
        String sql = "SELECT " +
                "m.id,m.`name`,m.sell_price,c.`name` AS categoryName,m.inventory,m.create_time,m.update_time " +
                "FROM medicine AS m LEFT JOIN category AS c ON m.category_id=c.id WHERE 1=1";
        List<Object> queryList = new ArrayList<>();
        String queryName = medicineQuery.getName();
        if(queryName != null && !"".equals(queryName)) {
            sql += " AND m.`name` LIKE ?";
            queryList.add("%" + queryName + "%");
        }
        Integer queryCategoryId = medicineQuery.getCategoryId();
        if(queryCategoryId != null) {
            sql += " AND m.category_id=?";
            queryList.add(queryCategoryId);
        }
        Double leastSellPrice = medicineQuery.getLeastSellPrice();
        Double mostSellPrice = medicineQuery.getMostSellPrice();
        if(leastSellPrice != null && mostSellPrice != null) {
            sql += " AND m.sell_price BETWEEN ? AND ?";
            queryList.add(leastSellPrice);
            queryList.add(mostSellPrice);
        }
        sql += " LIMIT ?,?";
        int offset = (medicineQuery.getPage() - 1) * medicineQuery.getLimit();
        queryList.add(offset);
        queryList.add(medicineQuery.getLimit());
        return template.query(sql, new BeanPropertyRowMapper<>(MedicineVO.class), queryList.toArray());
    }

    @Override
    public int selectTotalCount(MedicineQuery medicineQuery) {
        String sql = "SELECT COUNT(*) FROM medicine WHERE 1=1";
        List<Object> queryList = new ArrayList<>();
        String queryName = medicineQuery.getName();
        if(queryName != null && !"".equals(queryName)) {
            sql += " AND `name` LIKE ?";
            queryList.add("%" + queryName + "%");
        }
        Integer queryCategoryId = medicineQuery.getCategoryId();
        if(queryCategoryId != null) {
            sql += " AND category_id=?";
            queryList.add(queryCategoryId);
        }
        Double leastSellPrice = medicineQuery.getLeastSellPrice();
        Double mostSellPrice = medicineQuery.getMostSellPrice();
        if(leastSellPrice != null && mostSellPrice != null) {
            sql += " AND sell_price BETWEEN ? AND ?";
            queryList.add(leastSellPrice);
            queryList.add(mostSellPrice);
        }
        return template.queryForObject(sql, Integer.class, queryList.toArray());
    }

    @Override
    public void deleteById(int id) {
        String sql = "DELETE FROM medicine WHERE id=?";
        template.update(sql, id);
    }

    @Override
    public void add(Medicine medicine) {
        String sql = "INSERT INTO medicine(`name`,sell_price,category_id) VALUES(?,?,?)";
        template.update(sql, medicine.getName(), medicine.getSellPrice(), medicine.getCategoryId());
    }

    @Override
    public Medicine selectById(int id) {
        String sql = "SELECT id,`name`,sell_price,category_id,inventory,create_time,update_time FROM medicine WHERE id=?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<>(Medicine.class), id);
    }

    @Override
    public void update(Medicine medicine) {
        String sql = "UPDATE medicine SET name=?,sell_price=?,category_id=? WHERE id=?";
        template.update(sql, medicine.getName(), medicine.getSellPrice(), medicine.getCategoryId(), medicine.getId());
    }

    @Override
    public List<Medicine> selectAll() {
        String sql = "SELECT id,`name`,sell_price,category_id,inventory,create_time,update_time FROM medicine";
        return template.query(sql, new BeanPropertyRowMapper<>(Medicine.class));
    }

    @Override
    public void addExcel(Medicine medicine) {
        String sql = "INSERT INTO medicine(`name`,sell_price,category_id,inventory,create_time,update_time) VALUES(?,?,?,?,?,?)";
        template.update(sql, medicine.getName(), medicine.getSellPrice(), medicine.getCategoryId(), medicine.getInventory(), medicine.getCreateTime(), medicine.getUpdateTime());
    }

    @Override
    public void updateInventory(int id, int inventory) {
        String sql = "UPDATE medicine SET inventory=? WHERE id=?";
        template.update(sql, inventory, id);
    }

    @Override
    public List<MedicineIdVO> selectIds() {
        String sql = "SELECT id FROM medicine";
        return template.query(sql, new BeanPropertyRowMapper<>(MedicineIdVO.class));
    }
}
